PostgreSQL PGPOOL-II 配置

#PostgreSQL #PGPOOL-II #配置

1 背景知识

本章介绍如何配置 PGPOOL-II ，以及介绍相关的配置文件。

2 创建 pgpool_node_id 文件

watchdog 需要根据 pgpoo_node_id 判断每个服务器，所以需要提前配置。

su - postgres

cat > $PGHOME/etc/pgpool_node_id << EOF
0
EOF


cat > $PGHOME/etc/pgpool_node_id << EOF
1
EOF


cat > $PGHOME/etc/pgpool_node_id << EOF
2
EOF

3 配置 pgpool-II 脚本文件

3.1 创建失败切换脚本

su - postgres
cd $PGHOME/etc 
cp failover.sh.sample failover.sh
cp follow_primary.sh.sample follow_primary.sh
chmod u+x {failover.sh,follow_primary.sh}

3.2 创建在线恢复脚本

su - postgres
cp -p $PGHOME/etc/recovery_1st_stage.sample $PGDATA/recovery_1st_stage
cp -p $PGHOME/etc/pgpool_remote_start.sample $PGDATA/pgpool_remote_start
chmod u+x $PGDATA/{recovery_1st_stage,pgpool_remote_start}


cp -p $PGHOME/etc/escalation.sh.sample $PGHOME/etc/escalation.sh
chmod u+x escalation.sh

3.4 修改配置脚本

su - postgres

vi $PGHOME/etc/failover.sh
--------------------input------------------------------
PGHOME=/usr/local/pgsql
SSH_KEY_FILE=id_rsa

3.4.2 follow_primary.sh


vi $PGHOME/etc/follow_primary.sh

--------------------input------------------------------
PGPOOL_PATH=/usr/local/pgsql/bin
PGHOME=/usr/local/pgsql
ARCHIVEDIR=/archive
SSH_KEY_FILE=id_rsa

3.4.3 recovery_1st_stage

vi $PGDATA/recovery_1st_stage

--------------------input------------------------------
PGHOME=/usr/local/pgsql
ARCHIVEDIR=/archvie
SSH_KEY_FILE=id_rsa

3.4.4 pgpool_remote_start

 vi $PGDATA/pgpool_remote_start

--------------------input------------------------------
PGHOME=/usr/local/pgsql
SSH_KEY_FILE=id_rsa


su - postgres

vi $PGHOME/etc/escalation.sh

--------------------input------------------------------
SSH_KEY_FILE=id_rsa
PGPOOLS=(node1 node2 node3)
VIP=192.168.10.191
DEVICE=ens192

3.5 将配置脚本拷贝到每个节点

cd $PGHOME/etc/
scp follow_primary.sh failover.sh escalation.sh node2:$PGHOME/etc/
scp follow_primary.sh failover.sh escalation.sh node3:$PGHOME/etc/
scp $PGDATA/recovery_1st_stage $PGDATA/pgpool_remote_start node2:$PGDATA/
scp $PGDATA/recovery_1st_stage $PGDATA/pgpool_remote_start node3:$PGDATA/

4 配置pcp.conf

Warning

未有特殊说明，以下操作都在所有节点中执行。

4.1 编辑pcp.conf 文件

echo 'pgpool:'`pg_md5 pgpool` > $PGHOME/etc/pcp.conf
echo 'postgres:'`pg_md5 postgres` > $PGHOME/etc/pcp.conf

4.2 配置 pcppass 密码文件

echo *:9898:pgpool:pgpool > ~/.pcppass
chmod 600 ~/.pcppass

4.3 将pcp.conf 文件拷贝到其他节点

scp $PGHOME/etc/pcp.conf node2:$PGHOME/etc
scp $PGHOME/etc/pcp.conf node3:$PGHOME/etc
scp ~/.pcppass node2:~
scp ~/.pcppass node3:~

5 配置 pool_passwd 文件

5.1 创建.pgpoolkey文件，并生成密码文件

echo 'pgpool' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey
pg_enc -m -k ~/.pgpoolkey -u pgpool -p
pg_enc -m -k ~/.pgpoolkey -u postgres -p

5.2 将 pool_passwd 文件拷贝到其他节点

scp $PGHOME/etc/pool_passwd node2:$PGHOME/etc
scp $PGHOME/etc/pool_passwd node3:$PGHOME/etc
scp ~/.pgpoolkey node2:~
scp ~/.pgpoolkey node3:~

6 配置 pgpool.conf

6.1 编辑 pgpool.conf 文件

vi $PGHOME/etc/pgpool.conf

--------------------input------------------------------
# 流复制模式
backend_clustering_mode ='streaming_replication'
# 通用设置
pid_file_name='/usr/local/pgsql/run/pgpool.pid'
socket_dir = '/usr/local/pgsql/run'
pcp_socket_dir = '/usr/local/pgsql/run/'
listen_addresses = '*' 
pcp_listen_addresses = '*'

 # 端口
port = 9999

# 流复制检查
sr_check_user = 'pgpool'
sr_check_password = 'pgpool'

# 健康检查 
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = 'pgpool'

health_check_max_retries = 3
# 设置postgres 服务器
# - Backend Connection Settings -

backend_hostname0 = 'node1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'node2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'node3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/usr/local/pgsql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

# 失败切换配置
failover_command = '/usr/local/pgsql/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/usr/local/pgsql/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
# 在线恢复功能配置 
recovery_user = 'postgres'
recovery_password = 'postgres'
recovery_1st_stage_command = 'recovery_1st_stage'
# 客户端认证 
enable_pool_hba = on
# 看门狗配置 
use_watchdog = on
delegate_ip = '192.168.10.191'
if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
if_up_cmd = '/sbin/ip addr add 192.168.10.191/24 dev ens192 label ens192:0' 
if_down_cmd = '/sbin/ip addr del 192.168.10.191/24 dev ens192:0' 
arping_cmd = '/usr/sbin/arping -U 192.168.10.192 -w 1 -I ens192'

hostname0 = 'node1'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'node2'
wd_port1 = 9000
pgpool_port1 = 9999

hostname2 = 'node3'
wd_port2 = 9000
pgpool_port2 = 9999

wd_lifecheck_method = 'heartbeat'
wd_interval = 10

heartbeat_hostname0 = 'node1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'node2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'node3'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30

wd_escalation_command = '/usr/local/pgsql/etc/escalation.sh'
# 日志记录 配置
log_destination = 'stderr'
logging_collector = on
log_directory = '/usr/local/pgsql/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
Warning

注意： recovery_1st_stage脚本不支持表空间。如果您使用的是表空间，则需要修改脚本以支持表空间。

6.2 将 pgpool.conf 文件拷贝到其他节点

scp $PGHOME/etc/pgpool.conf node2:$PGHOME/etc/
scp $PGHOME/etc/pgpool.conf node3:$PGHOME/etc/

7 客户端认证配置 pool_hba.conf

7.1 编辑pcp.conf文件

cd $PGHOME/etc
cp pool_hba.conf.sample pool_hba.conf
vi pool_hba.conf
host    all         all           0.0.0.0/0         scram-sha-256

7.2 生成 pgpool-II 所使用的密码

cd $PGHOME/etc
 echo 'some string' > ~/.pgpoolkey
 chmod 600 ~/.pgpoolkey
pg_enc -m -k ~/.pgpoolkey -u pgpool -p -f $PGHOME/etc/pgpool.conf
db password:pgpool
 pg_enc -m -k ~/.pgpoolkey -u postgres -p -f $PGHOME/etc/pgpool.conf
db password:postgrs

7.3 将pcp.conf 文件拷贝到其他节点

cd $PGHOME/etc
scp pool_hba.conf  pool_passwd node2:$PGHOME/etc
scp pool_hba.conf  pool_passwd node3:$PGHOME/etc
scp ~/.pgpoolkey node2:~
scp ~/.pgpoolkey node3:~

8 配置 ip 和 arping 命令的 setuid

chmod u+s /sbin/ip 
chmod u+s /usr/sbin/arping
ls -ltra /sbin/ip
ls -ltra /usr/sbin/arping
Note

或者将postgres 用户加入到 sudoer 名单中，并且不需要输入密码。

9 配置 pgpool-II 所需的目录

Warning

未有特殊说明，以下操作都在所有节点中执行。

su - postgres 
mkdir /usr/local/pgsql/log/
mkdir /usr/local/pgsql/run

10 pgpool 启动与停止

10.1 停止 pgpool

Warning

先关备库，再关主库，关闭顺序不对则会造成主备切换。


pgpool -f $PGHOME/etc/pgpool.conf -F $PGHOME/etc/pcp.conf -a $PGHOME/etc/pg_hba.conf -m fast stop

10.2 启动pgpool

Warning

先启主库，再启备库，启动顺序不对则会造成主备切换。

pgpool -f $PGHOME/etc/pgpool.conf -F $PGHOME/etc/pcp.conf -a $PGHOME/etc/pool_hba.conf

到此为止：打快照 pgpool-II cluster。

11 设置备用服务器

pcp_recovery_node -h vip -p 9898 -U pgpool -n 1

 psql -h vip -p 9999 -U pgpool postgres -c "show pool_nodes"

12 主备切换

12.1 查看集群状态

pcp_watchdog_info -h vip -p 9898 -U pgpool
Password:
//屏幕输出：
3 3 YES node1:9999 Linux node1 node1

node1:9999 Linux node1 node1 9999 9000 4 LEADER 0 MEMBER
node2:9999 Linux node2 node2 9999 9000 7 STANDBY 0 MEMBER
node3:9999 Linux node3 node3 9999 9000 7 STANDBY 0 MEMBER

12.2 node1 关闭pgpool 服务

pgpool -f $PGHOME/etc/pgpool.conf -F $PGHOME/etc/pcp.conf -a $PGHOME/etc/pg_hba.conf -m fast stop
Warning

这里可以关闭数据库，操作系统，pgpool-II 服务，都可以进行主备切换。

12.3 查看集群状态

pcp_watchdog_info -h vip -p 9898 -U pgpool
Password:

//屏幕输出：
3 3 YES node2:9999 Linux node2 node2

node2:9999 Linux node2 node2 9999 9000 4 LEADER 0 MEMBER
node1:9999 Linux node1 node1 9999 9000 10 SHUTDOWN 0 MEMBER
node3:9999 Linux node3 node3 9999 9000 7 STANDBY 0 MEMBER

可以看到 node1 已经 SHUTDOWN ，node2 成为了新的 master 。

12.4 启动node1 上的 pgpool-II 服务

验证 node1 将会以备库的方式重新加入到集群中。

pgpool -f $PGHOME/etc/pgpool.conf -F $PGHOME/etc/pcp.conf -a $PGHOME/etc/pool_hba.conf

12.5 查看集群状态

pcp_watchdog_info -h vip -p 9898 -U pgpool
Password:

//屏幕输出：
3 3 YES node2:9999 Linux node2 node2

node2:9999 Linux node2 node2 9999 9000 4 LEADER 0 MEMBER
node1:9999 Linux node1 node1 9999 9000 7 STANDBY 0 MEMBER
node3:9999 Linux node3 node3 9999 9000 7 STANDBY 0 MEMBER